--DO NOT MODIFY THIS FILE. IT IS ALWAYS OVERWRITTEN ON GENERATION.
--Data Schema For Version 0.0.0.0
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_SupplierDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_SupplierDelete]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_SupplierDelete]
(
	@Original_SupplierID Int
)
AS
SET NOCOUNT OFF;

DELETE FROM 
	[Suppliers] 
WHERE 
	[SupplierID] = @Original_SupplierID ;

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_SupplierSelectBySupplierPks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_SupplierSelectBySupplierPks]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [dbo].[gen_SupplierSelectBySupplierPks]
(
	@xml ntext
)
AS

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml

SELECT
		[Suppliers].[supplierid],
	[Suppliers].[region],
	[Suppliers].[postalcode],
	[Suppliers].[phone],
	[Suppliers].[homepage],
	[Suppliers].[fax],
	[Suppliers].[country],
	[Suppliers].[contacttitle],
	[Suppliers].[contactname],
	[Suppliers].[companyname],
	[Suppliers].[city],
	[Suppliers].[address],
	[Suppliers].[created_by],
	[Suppliers].[created_date],
	[Suppliers].[modified_by],
	[Suppliers].[modified_date],
	[Suppliers].[time_stamp]

FROM 
[Suppliers]
WHERE

	[Suppliers].[SupplierID] IN (SELECT [SupplierID] 
											FROM OpenXML(@hDoc, '//Suppliers', 2) 
											WITH ([SupplierID] char(36) 'SupplierID')) 


exec sp_xml_removeDocument @hDoc

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_SupplierSelectBySupplierSinglePk]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_SupplierSelectBySupplierSinglePk]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_SupplierSelectBySupplierSinglePk]
(
	@SupplierID Int
)
AS

SELECT 
	[Suppliers].[supplierid],
	[Suppliers].[region],
	[Suppliers].[postalcode],
	[Suppliers].[phone],
	[Suppliers].[homepage],
	[Suppliers].[fax],
	[Suppliers].[country],
	[Suppliers].[contacttitle],
	[Suppliers].[contactname],
	[Suppliers].[companyname],
	[Suppliers].[city],
	[Suppliers].[address],
	[Suppliers].[created_by],
	[Suppliers].[created_date],
	[Suppliers].[modified_by],
	[Suppliers].[modified_date],
	[Suppliers].[time_stamp]

FROM
[Suppliers]
WHERE 
[Suppliers].[SupplierID] = @SupplierID 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_SupplierInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_SupplierInsert]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[gen_SupplierInsert]
(
	@SupplierID Int = default,
	@Region NVarChar(15) = default,
	@PostalCode NVarChar(10) = default,
	@Phone NVarChar(24) = default,
	@HomePage NText = default,
	@Fax NVarChar(24) = default,
	@Country NVarChar(15) = default,
	@ContactTitle NVarChar(30) = default,
	@ContactName NVarChar(30) = default,
	@CompanyName NVarChar(40) = default,
	@City NVarChar(15) = default,
	@Address NVarChar(60) = default,
	@created_date datetime,
	@created_by varchar (50),
	@modified_by varchar (50)

)
AS
SET NOCOUNT OFF;

if (@created_date IS NULL)
SET @created_date = GetDate()
IF (@SupplierID < 0) SET @SupplierID = NULL;
if ((@SupplierID IS NULL))
BEGIN

INSERT INTO [Suppliers]
(
[Address],
[City],
[CompanyName],
[ContactName],
[ContactTitle],
[Country],
[Fax],
[HomePage],
[Phone],
[PostalCode],
[Region],
[created_date],
[created_by],
[modified_date],
[modified_by]

)
VALUES
(
	@Address,
	@City,
	@CompanyName,
	@ContactName,
	@ContactTitle,
	@Country,
	@Fax,
	@HomePage,
	@Phone,
	@PostalCode,
	@Region,
	@created_date,
	@created_by,
	@created_date,
	@modified_by

);

END
ELSE
BEGIN
SET identity_insert [Suppliers] on
INSERT INTO [Suppliers]
(
[SupplierID],
[Address],
[City],
[CompanyName],
[ContactName],
[ContactTitle],
[Country],
[Fax],
[HomePage],
[Phone],
[PostalCode],
[Region],
[created_date],
[created_by],
[modified_date],
[modified_by]

)
VALUES
(
	@SupplierID,
	@Address,
	@City,
	@CompanyName,
	@ContactName,
	@ContactTitle,
	@Country,
	@Fax,
	@HomePage,
	@Phone,
	@PostalCode,
	@Region,
	@created_date,
	@created_by,
	@created_date,
	@modified_by

);
SET identity_insert [Suppliers] off
END


SELECT 
	[Suppliers].[supplierid],
	[Suppliers].[region],
	[Suppliers].[postalcode],
	[Suppliers].[phone],
	[Suppliers].[homepage],
	[Suppliers].[fax],
	[Suppliers].[country],
	[Suppliers].[contacttitle],
	[Suppliers].[contactname],
	[Suppliers].[companyname],
	[Suppliers].[city],
	[Suppliers].[address],
	[Suppliers].[created_by],
	[Suppliers].[created_date],
	[Suppliers].[modified_by],
	[Suppliers].[modified_date],
	[Suppliers].[time_stamp]

FROM
[Suppliers]
WHERE
	[Suppliers].[SupplierID] = SCOPE_IDENTITY();
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_SupplierPagingSelect]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_SupplierPagingSelect]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_SupplierPagingSelect]
(
	@page int, -- page number selected by the user
	@pageSize int, -- number of items on the page
	@orderByColumn varchar(100), -- name of column to order things by
	@ascending bit, -- order column ascending or descending
	@filter varchar(100) = null, -- filter statement passed in to determine like criteria on order by column
	@count int out -- number of items in the collection
)
AS

SET NOCOUNT ON;


CREATE TABLE #tmpTable
(
[SupplierID] Int
)

DECLARE @total__ivqatedr int
DECLARE @orderByColumnIndex int
-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@pageSize * @page)
IF (@total__ivqatedr <> 0)
BEGIN
	SET ROWCOUNT @total__ivqatedr
END
INSERT INTO #tmpTable
(
	[supplierid]
)
SELECT
	[Suppliers].[supplierid]
FROM
[Suppliers]
WHERE
	(@orderByColumn = 'SupplierID' and (((@filter is null) or ([Suppliers].[SupplierID] is null)) or (@filter is not null and [Suppliers].[SupplierID] = @filter)))
or
	(@orderByColumn = 'Region' and (((@filter is null) or ([Suppliers].[Region] is null)) or (@filter is not null and [Suppliers].[Region] LIKE @filter)))
or
	(@orderByColumn = 'PostalCode' and (((@filter is null) or ([Suppliers].[PostalCode] is null)) or (@filter is not null and [Suppliers].[PostalCode] LIKE @filter)))
or
	(@orderByColumn = 'Phone' and (((@filter is null) or ([Suppliers].[Phone] is null)) or (@filter is not null and [Suppliers].[Phone] LIKE @filter)))
or
	(@orderByColumn = 'Fax' and (((@filter is null) or ([Suppliers].[Fax] is null)) or (@filter is not null and [Suppliers].[Fax] LIKE @filter)))
or
	(@orderByColumn = 'Country' and (((@filter is null) or ([Suppliers].[Country] is null)) or (@filter is not null and [Suppliers].[Country] LIKE @filter)))
or
	(@orderByColumn = 'ContactTitle' and (((@filter is null) or ([Suppliers].[ContactTitle] is null)) or (@filter is not null and [Suppliers].[ContactTitle] LIKE @filter)))
or
	(@orderByColumn = 'ContactName' and (((@filter is null) or ([Suppliers].[ContactName] is null)) or (@filter is not null and [Suppliers].[ContactName] LIKE @filter)))
or
	(@orderByColumn = 'CompanyName' and (((@filter is null) or ([Suppliers].[CompanyName] is null)) or (@filter is not null and [Suppliers].[CompanyName] LIKE @filter)))
or
	(@orderByColumn = 'City' and (((@filter is null) or ([Suppliers].[City] is null)) or (@filter is not null and [Suppliers].[City] LIKE @filter)))
or
	(@orderByColumn = 'Address' and (((@filter is null) or ([Suppliers].[Address] is null)) or (@filter is not null and [Suppliers].[Address] LIKE @filter)))
ORDER BY
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'SupplierID' THEN [Suppliers].[SupplierID] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'SupplierID' THEN [Suppliers].[SupplierID] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'Region' THEN [Suppliers].[Region] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'Region' THEN [Suppliers].[Region] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'PostalCode' THEN [Suppliers].[PostalCode] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'PostalCode' THEN [Suppliers].[PostalCode] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'Phone' THEN [Suppliers].[Phone] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'Phone' THEN [Suppliers].[Phone] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'Fax' THEN [Suppliers].[Fax] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'Fax' THEN [Suppliers].[Fax] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'Country' THEN [Suppliers].[Country] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'Country' THEN [Suppliers].[Country] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'ContactTitle' THEN [Suppliers].[ContactTitle] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'ContactTitle' THEN [Suppliers].[ContactTitle] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'ContactName' THEN [Suppliers].[ContactName] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'ContactName' THEN [Suppliers].[ContactName] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'CompanyName' THEN [Suppliers].[CompanyName] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'CompanyName' THEN [Suppliers].[CompanyName] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'City' THEN [Suppliers].[City] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'City' THEN [Suppliers].[City] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'Address' THEN [Suppliers].[Address] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'Address' THEN [Suppliers].[Address] END END

-- set @count based on the rows moved in the previous statement
SET ROWCOUNT 0
SET @count = (
SELECT count(*)
FROM
[Suppliers]
WHERE
	(@orderByColumn = 'SupplierID' and (((@filter is null) or ([Suppliers].[SupplierID] is null)) or (@filter is not null and [Suppliers].[SupplierID] = @filter)))
or
	(@orderByColumn = 'Region' and (((@filter is null) or ([Suppliers].[Region] is null)) or (@filter is not null and [Suppliers].[Region] LIKE @filter)))
or
	(@orderByColumn = 'PostalCode' and (((@filter is null) or ([Suppliers].[PostalCode] is null)) or (@filter is not null and [Suppliers].[PostalCode] LIKE @filter)))
or
	(@orderByColumn = 'Phone' and (((@filter is null) or ([Suppliers].[Phone] is null)) or (@filter is not null and [Suppliers].[Phone] LIKE @filter)))
or
	(@orderByColumn = 'Fax' and (((@filter is null) or ([Suppliers].[Fax] is null)) or (@filter is not null and [Suppliers].[Fax] LIKE @filter)))
or
	(@orderByColumn = 'Country' and (((@filter is null) or ([Suppliers].[Country] is null)) or (@filter is not null and [Suppliers].[Country] LIKE @filter)))
or
	(@orderByColumn = 'ContactTitle' and (((@filter is null) or ([Suppliers].[ContactTitle] is null)) or (@filter is not null and [Suppliers].[ContactTitle] LIKE @filter)))
or
	(@orderByColumn = 'ContactName' and (((@filter is null) or ([Suppliers].[ContactName] is null)) or (@filter is not null and [Suppliers].[ContactName] LIKE @filter)))
or
	(@orderByColumn = 'CompanyName' and (((@filter is null) or ([Suppliers].[CompanyName] is null)) or (@filter is not null and [Suppliers].[CompanyName] LIKE @filter)))
or
	(@orderByColumn = 'City' and (((@filter is null) or ([Suppliers].[City] is null)) or (@filter is not null and [Suppliers].[City] LIKE @filter)))
or
	(@orderByColumn = 'Address' and (((@filter is null) or ([Suppliers].[Address] is null)) or (@filter is not null and [Suppliers].[Address] LIKE @filter)))
)

-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@pageSize * @page) - @pageSize
IF (@total__ivqatedr <> 0)
BEGIN
	SET ROWCOUNT @total__ivqatedr
	DELETE FROM #tmpTable
END

-- return the number of rows requested as the page size
SET ROWCOUNT @pageSize
SELECT
	[Suppliers].[supplierid],
	[Suppliers].[region],
	[Suppliers].[postalcode],
	[Suppliers].[phone],
	[Suppliers].[homepage],
	[Suppliers].[fax],
	[Suppliers].[country],
	[Suppliers].[contacttitle],
	[Suppliers].[contactname],
	[Suppliers].[companyname],
	[Suppliers].[city],
	[Suppliers].[address],
	[Suppliers].[created_by],
	[Suppliers].[created_date],
	[Suppliers].[modified_by],
	[Suppliers].[modified_date],
	[Suppliers].[time_stamp]
FROM
	[#tmpTable]
	INNER JOIN [Suppliers] ON #tmpTable.[supplierid] = [SUPPLIERS].[supplierid]
ORDER BY
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'SupplierID' THEN [Suppliers].[SupplierID] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'SupplierID' THEN [Suppliers].[SupplierID] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'Region' THEN [Suppliers].[Region] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'Region' THEN [Suppliers].[Region] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'PostalCode' THEN [Suppliers].[PostalCode] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'PostalCode' THEN [Suppliers].[PostalCode] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'Phone' THEN [Suppliers].[Phone] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'Phone' THEN [Suppliers].[Phone] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'Fax' THEN [Suppliers].[Fax] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'Fax' THEN [Suppliers].[Fax] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'Country' THEN [Suppliers].[Country] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'Country' THEN [Suppliers].[Country] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'ContactTitle' THEN [Suppliers].[ContactTitle] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'ContactTitle' THEN [Suppliers].[ContactTitle] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'ContactName' THEN [Suppliers].[ContactName] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'ContactName' THEN [Suppliers].[ContactName] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'CompanyName' THEN [Suppliers].[CompanyName] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'CompanyName' THEN [Suppliers].[CompanyName] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'City' THEN [Suppliers].[City] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'City' THEN [Suppliers].[City] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'Address' THEN [Suppliers].[Address] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'Address' THEN [Suppliers].[Address] END END

DROP TABLE #tmpTable

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_SupplierSelectBySearchAnd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_SupplierSelectBySearchAnd]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_SupplierSelectBySearchAnd]
(
	@SupplierID Int = null, 
	@Region NVarChar(15) = null, 
	@PostalCode NVarChar(10) = null, 
	@Phone NVarChar(24) = null, 
	@Fax NVarChar(24) = null, 
	@Country NVarChar(15) = null, 
	@ContactTitle NVarChar(30) = null, 
	@ContactName NVarChar(30) = null, 
	@CompanyName NVarChar(40) = null, 
	@City NVarChar(15) = null, 
	@Address NVarChar(60) = null, 
	@paging_PageIndex int = -1, -- page number selected by the user
	@paging_RecordsperPage int = -1, -- number of items on the page
	@paging_OrderByColumn varchar(100) = '', -- name of column to order things by
	@paging_Ascending bit = 1, -- order column ascending or descending
	@paging_Count int out -- number of items in the collection
)
AS

CREATE TABLE #tmpTable 
(
	[SupplierID] Int
)

DECLARE @total__ivqatedr int
DECLARE @orderByColumnIndex int
-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@paging_RecordsperPage * @paging_PageIndex)
IF (@total__ivqatedr <> 0) AND (@paging_PageIndex <> -1)
BEGIN
	SET ROWCOUNT @total__ivqatedr
END
INSERT INTO #tmpTable
(
	[supplierid]
)
SELECT
	[Suppliers].[supplierid]
FROM
[Suppliers]
WHERE
((@SupplierID is null) OR ([Suppliers].[SupplierID] = @SupplierID)) AND
((@Region is null) OR ([Suppliers].[Region] LIKE @Region)) AND
((@PostalCode is null) OR ([Suppliers].[PostalCode] LIKE @PostalCode)) AND
((@Phone is null) OR ([Suppliers].[Phone] LIKE @Phone)) AND
((@Fax is null) OR ([Suppliers].[Fax] LIKE @Fax)) AND
((@Country is null) OR ([Suppliers].[Country] LIKE @Country)) AND
((@ContactTitle is null) OR ([Suppliers].[ContactTitle] LIKE @ContactTitle)) AND
((@ContactName is null) OR ([Suppliers].[ContactName] LIKE @ContactName)) AND
((@CompanyName is null) OR ([Suppliers].[CompanyName] LIKE @CompanyName)) AND
((@City is null) OR ([Suppliers].[City] LIKE @City)) AND
((@Address is null) OR ([Suppliers].[Address] LIKE @Address))
ORDER BY
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'SupplierID' THEN [Suppliers].[SupplierID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'SupplierID' THEN [Suppliers].[SupplierID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Region' THEN [Suppliers].[Region] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Region' THEN [Suppliers].[Region] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'PostalCode' THEN [Suppliers].[PostalCode] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'PostalCode' THEN [Suppliers].[PostalCode] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Phone' THEN [Suppliers].[Phone] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Phone' THEN [Suppliers].[Phone] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Fax' THEN [Suppliers].[Fax] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Fax' THEN [Suppliers].[Fax] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Country' THEN [Suppliers].[Country] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Country' THEN [Suppliers].[Country] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ContactTitle' THEN [Suppliers].[ContactTitle] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ContactTitle' THEN [Suppliers].[ContactTitle] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ContactName' THEN [Suppliers].[ContactName] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ContactName' THEN [Suppliers].[ContactName] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'CompanyName' THEN [Suppliers].[CompanyName] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'CompanyName' THEN [Suppliers].[CompanyName] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'City' THEN [Suppliers].[City] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'City' THEN [Suppliers].[City] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Address' THEN [Suppliers].[Address] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Address' THEN [Suppliers].[Address] END END

	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Suppliers].[created_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Suppliers].[created_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Suppliers].[created_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Suppliers].[created_date] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Suppliers].[modified_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Suppliers].[modified_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Suppliers].[modified_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Suppliers].[modified_date] END END
-- set @paging_Count based on the rows moved in the previous statement
SET ROWCOUNT 0
SET @paging_Count = (
SELECT count(*)
FROM
[Suppliers]
WHERE
((@SupplierID is null) OR ([Suppliers].[SupplierID] = @SupplierID)) AND
((@Region is null) OR ([Suppliers].[Region] LIKE @Region)) AND
((@PostalCode is null) OR ([Suppliers].[PostalCode] LIKE @PostalCode)) AND
((@Phone is null) OR ([Suppliers].[Phone] LIKE @Phone)) AND
((@Fax is null) OR ([Suppliers].[Fax] LIKE @Fax)) AND
((@Country is null) OR ([Suppliers].[Country] LIKE @Country)) AND
((@ContactTitle is null) OR ([Suppliers].[ContactTitle] LIKE @ContactTitle)) AND
((@ContactName is null) OR ([Suppliers].[ContactName] LIKE @ContactName)) AND
((@CompanyName is null) OR ([Suppliers].[CompanyName] LIKE @CompanyName)) AND
((@City is null) OR ([Suppliers].[City] LIKE @City)) AND
((@Address is null) OR ([Suppliers].[Address] LIKE @Address))
)

-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@paging_RecordsperPage * @paging_PageIndex) - @paging_RecordsperPage
IF (@total__ivqatedr <> 0) AND (@paging_PageIndex <> -1)
BEGIN
	SET ROWCOUNT @total__ivqatedr
	DELETE FROM #tmpTable
END

-- return the number of rows requested as the page size
IF (@paging_PageIndex <> -1)
BEGIN
SET ROWCOUNT @paging_RecordsperPage
END
SELECT
	[Suppliers].[supplierid],
	[Suppliers].[region],
	[Suppliers].[postalcode],
	[Suppliers].[phone],
	[Suppliers].[homepage],
	[Suppliers].[fax],
	[Suppliers].[country],
	[Suppliers].[contacttitle],
	[Suppliers].[contactname],
	[Suppliers].[companyname],
	[Suppliers].[city],
	[Suppliers].[address],
	[Suppliers].[created_by],
	[Suppliers].[created_date],
	[Suppliers].[modified_by],
	[Suppliers].[modified_date],
	[Suppliers].[time_stamp]
FROM
	[#tmpTable]
	INNER JOIN [Suppliers] ON #tmpTable.[supplierid] = [SUPPLIERS].[supplierid]
ORDER BY
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'SupplierID' THEN [Suppliers].[SupplierID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'SupplierID' THEN [Suppliers].[SupplierID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Region' THEN [Suppliers].[Region] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Region' THEN [Suppliers].[Region] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'PostalCode' THEN [Suppliers].[PostalCode] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'PostalCode' THEN [Suppliers].[PostalCode] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Phone' THEN [Suppliers].[Phone] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Phone' THEN [Suppliers].[Phone] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Fax' THEN [Suppliers].[Fax] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Fax' THEN [Suppliers].[Fax] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Country' THEN [Suppliers].[Country] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Country' THEN [Suppliers].[Country] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ContactTitle' THEN [Suppliers].[ContactTitle] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ContactTitle' THEN [Suppliers].[ContactTitle] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ContactName' THEN [Suppliers].[ContactName] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ContactName' THEN [Suppliers].[ContactName] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'CompanyName' THEN [Suppliers].[CompanyName] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'CompanyName' THEN [Suppliers].[CompanyName] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'City' THEN [Suppliers].[City] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'City' THEN [Suppliers].[City] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Address' THEN [Suppliers].[Address] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Address' THEN [Suppliers].[Address] END END

	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Suppliers].[created_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Suppliers].[created_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Suppliers].[created_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Suppliers].[created_date] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Suppliers].[modified_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Suppliers].[modified_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Suppliers].[modified_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Suppliers].[modified_date] END END
DROP TABLE #tmpTable

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_SupplierUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_SupplierUpdate]
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_SupplierUpdate]
(
	@Region NVarChar(15),
	@PostalCode NVarChar(10),
	@Phone NVarChar(24),
	@HomePage NText,
	@Fax NVarChar(24),
	@Country NVarChar(15),
	@ContactTitle NVarChar(30),
	@ContactName NVarChar(30),
	@CompanyName NVarChar(40),
	@City NVarChar(15),
	@Address NVarChar(60),
	@modified_by varchar (50),
	@modified_date datetime = null,
	@Original_SupplierID Int,
	@Original_time_stamp timestamp

)
AS

IF (@modified_date IS NULL)
SET @modified_date = GetDate();

SET NOCOUNT OFF;
UPDATE 
[Suppliers] 
SET
[Address] = @Address,
[City] = @City,
[CompanyName] = @CompanyName,
[ContactName] = @ContactName,
[ContactTitle] = @ContactTitle,
[Country] = @Country,
[Fax] = @Fax,
[HomePage] = @HomePage,
[Phone] = @Phone,
[PostalCode] = @PostalCode,
[Region] = @Region,
[modified_by] = @modified_by,
[modified_date] = @modified_date

WHERE
	[Suppliers].[SupplierID] = @Original_SupplierID AND
	[Suppliers].[time_stamp] = @Original_time_stamp


SELECT
	[Suppliers].[supplierid],
	[Suppliers].[region],
	[Suppliers].[postalcode],
	[Suppliers].[phone],
	[Suppliers].[homepage],
	[Suppliers].[fax],
	[Suppliers].[country],
	[Suppliers].[contacttitle],
	[Suppliers].[contactname],
	[Suppliers].[companyname],
	[Suppliers].[city],
	[Suppliers].[address],
	[Suppliers].[created_by],
	[Suppliers].[created_date],
	[Suppliers].[modified_by],
	[Suppliers].[modified_date],
	[Suppliers].[time_stamp]
FROM 
[Suppliers]
WHERE
	[Suppliers].[SupplierID] = @Original_SupplierID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_SupplierSelectByCreatedDateRange]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_SupplierSelectByCreatedDateRange]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_SupplierSelectByCreatedDateRange]
(
	@start_date datetime,
	@end_date datetime
)
AS

SET NOCOUNT ON;

SELECT
		[Suppliers].[supplierid],
	[Suppliers].[region],
	[Suppliers].[postalcode],
	[Suppliers].[phone],
	[Suppliers].[homepage],
	[Suppliers].[fax],
	[Suppliers].[country],
	[Suppliers].[contacttitle],
	[Suppliers].[contactname],
	[Suppliers].[companyname],
	[Suppliers].[city],
	[Suppliers].[address],
	[Suppliers].[created_by],
	[Suppliers].[created_date],
	[Suppliers].[modified_by],
	[Suppliers].[modified_date],
	[Suppliers].[time_stamp]

FROM
[Suppliers]
WHERE
((([Suppliers].[created_date] IS NULL) AND (@start_date IS NULL)) OR (@start_date <= [Suppliers].[created_date])) AND 
((([Suppliers].[created_date] IS NULL) AND (@end_date IS NULL)) OR (@end_date >= [Suppliers].[created_date]))
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_SupplierSelectByModifiedDateRange]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_SupplierSelectByModifiedDateRange]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_SupplierSelectByModifiedDateRange]
(
	@start_date datetime,
	@end_date datetime
)
AS

SET NOCOUNT ON;

SELECT
		[Suppliers].[supplierid],
	[Suppliers].[region],
	[Suppliers].[postalcode],
	[Suppliers].[phone],
	[Suppliers].[homepage],
	[Suppliers].[fax],
	[Suppliers].[country],
	[Suppliers].[contacttitle],
	[Suppliers].[contactname],
	[Suppliers].[companyname],
	[Suppliers].[city],
	[Suppliers].[address],
	[Suppliers].[created_by],
	[Suppliers].[created_date],
	[Suppliers].[modified_by],
	[Suppliers].[modified_date],
	[Suppliers].[time_stamp]

FROM
[Suppliers]
WHERE
((([Suppliers].[modified_date] IS NULL) AND (@start_date IS NULL)) OR (@start_date <= [Suppliers].[modified_date])) AND 
((([Suppliers].[modified_date] IS NULL) AND (@end_date IS NULL)) OR (@end_date >= [Suppliers].[modified_date]))
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_SupplierSelectBySearchOr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_SupplierSelectBySearchOr]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_SupplierSelectBySearchOr]
(
	@SupplierID Int = null, 
	@Region NVarChar(15) = null, 
	@PostalCode NVarChar(10) = null, 
	@Phone NVarChar(24) = null, 
	@Fax NVarChar(24) = null, 
	@Country NVarChar(15) = null, 
	@ContactTitle NVarChar(30) = null, 
	@ContactName NVarChar(30) = null, 
	@CompanyName NVarChar(40) = null, 
	@City NVarChar(15) = null, 
	@Address NVarChar(60) = null, 
	@paging_PageIndex int = -1, -- page number selected by the user
	@paging_RecordsperPage int = -1, -- number of items on the page
	@paging_OrderByColumn varchar(100) = '', -- name of column to order things by
	@paging_Ascending bit = 1, -- order column ascending or descending
	@paging_Count int out -- number of items in the collection
)
AS

CREATE TABLE #tmpTable 
(
	[SupplierID] Int
)

DECLARE @total__ivqatedr int
DECLARE @orderByColumnIndex int
-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@paging_RecordsperPage * @paging_PageIndex)
IF (@total__ivqatedr <> 0) AND (@paging_PageIndex <> -1)
BEGIN
	SET ROWCOUNT @total__ivqatedr
END
INSERT INTO #tmpTable
(
	[supplierid]
)
SELECT
	[Suppliers].[supplierid]
FROM
[Suppliers]
WHERE
((@SupplierID is null) OR ([Suppliers].[SupplierID] = @SupplierID)) OR
((@Region is null) OR ([Suppliers].[Region] LIKE @Region)) OR
((@PostalCode is null) OR ([Suppliers].[PostalCode] LIKE @PostalCode)) OR
((@Phone is null) OR ([Suppliers].[Phone] LIKE @Phone)) OR
((@Fax is null) OR ([Suppliers].[Fax] LIKE @Fax)) OR
((@Country is null) OR ([Suppliers].[Country] LIKE @Country)) OR
((@ContactTitle is null) OR ([Suppliers].[ContactTitle] LIKE @ContactTitle)) OR
((@ContactName is null) OR ([Suppliers].[ContactName] LIKE @ContactName)) OR
((@CompanyName is null) OR ([Suppliers].[CompanyName] LIKE @CompanyName)) OR
((@City is null) OR ([Suppliers].[City] LIKE @City)) OR
((@Address is null) OR ([Suppliers].[Address] LIKE @Address))
ORDER BY
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'SupplierID' THEN [Suppliers].[SupplierID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'SupplierID' THEN [Suppliers].[SupplierID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Region' THEN [Suppliers].[Region] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Region' THEN [Suppliers].[Region] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'PostalCode' THEN [Suppliers].[PostalCode] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'PostalCode' THEN [Suppliers].[PostalCode] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Phone' THEN [Suppliers].[Phone] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Phone' THEN [Suppliers].[Phone] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Fax' THEN [Suppliers].[Fax] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Fax' THEN [Suppliers].[Fax] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Country' THEN [Suppliers].[Country] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Country' THEN [Suppliers].[Country] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ContactTitle' THEN [Suppliers].[ContactTitle] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ContactTitle' THEN [Suppliers].[ContactTitle] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ContactName' THEN [Suppliers].[ContactName] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ContactName' THEN [Suppliers].[ContactName] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'CompanyName' THEN [Suppliers].[CompanyName] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'CompanyName' THEN [Suppliers].[CompanyName] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'City' THEN [Suppliers].[City] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'City' THEN [Suppliers].[City] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Address' THEN [Suppliers].[Address] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Address' THEN [Suppliers].[Address] END END

	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Suppliers].[created_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Suppliers].[created_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Suppliers].[created_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Suppliers].[created_date] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Suppliers].[modified_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Suppliers].[modified_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Suppliers].[modified_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Suppliers].[modified_date] END END
-- set @paging_Count based on the rows moved in the previous statement
SET ROWCOUNT 0
SET @paging_Count = (
SELECT count(*)
FROM
[Suppliers]
WHERE
((@SupplierID is null) OR ([Suppliers].[SupplierID] = @SupplierID)) OR
((@Region is null) OR ([Suppliers].[Region] LIKE @Region)) OR
((@PostalCode is null) OR ([Suppliers].[PostalCode] LIKE @PostalCode)) OR
((@Phone is null) OR ([Suppliers].[Phone] LIKE @Phone)) OR
((@Fax is null) OR ([Suppliers].[Fax] LIKE @Fax)) OR
((@Country is null) OR ([Suppliers].[Country] LIKE @Country)) OR
((@ContactTitle is null) OR ([Suppliers].[ContactTitle] LIKE @ContactTitle)) OR
((@ContactName is null) OR ([Suppliers].[ContactName] LIKE @ContactName)) OR
((@CompanyName is null) OR ([Suppliers].[CompanyName] LIKE @CompanyName)) OR
((@City is null) OR ([Suppliers].[City] LIKE @City)) OR
((@Address is null) OR ([Suppliers].[Address] LIKE @Address))
)

-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@paging_RecordsperPage * @paging_PageIndex) - @paging_RecordsperPage
IF (@total__ivqatedr <> 0) AND (@paging_PageIndex <> -1)
BEGIN
	SET ROWCOUNT @total__ivqatedr
	DELETE FROM #tmpTable
END

-- return the number of rows requested as the page size
IF (@paging_PageIndex <> -1)
BEGIN
SET ROWCOUNT @paging_RecordsperPage
END
SELECT
	[Suppliers].[supplierid],
	[Suppliers].[region],
	[Suppliers].[postalcode],
	[Suppliers].[phone],
	[Suppliers].[homepage],
	[Suppliers].[fax],
	[Suppliers].[country],
	[Suppliers].[contacttitle],
	[Suppliers].[contactname],
	[Suppliers].[companyname],
	[Suppliers].[city],
	[Suppliers].[address],
	[Suppliers].[created_by],
	[Suppliers].[created_date],
	[Suppliers].[modified_by],
	[Suppliers].[modified_date],
	[Suppliers].[time_stamp]
FROM
	[#tmpTable]
	INNER JOIN [Suppliers] ON #tmpTable.[supplierid] = [SUPPLIERS].[supplierid]
ORDER BY
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'SupplierID' THEN [Suppliers].[SupplierID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'SupplierID' THEN [Suppliers].[SupplierID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Region' THEN [Suppliers].[Region] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Region' THEN [Suppliers].[Region] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'PostalCode' THEN [Suppliers].[PostalCode] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'PostalCode' THEN [Suppliers].[PostalCode] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Phone' THEN [Suppliers].[Phone] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Phone' THEN [Suppliers].[Phone] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Fax' THEN [Suppliers].[Fax] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Fax' THEN [Suppliers].[Fax] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Country' THEN [Suppliers].[Country] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Country' THEN [Suppliers].[Country] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ContactTitle' THEN [Suppliers].[ContactTitle] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ContactTitle' THEN [Suppliers].[ContactTitle] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ContactName' THEN [Suppliers].[ContactName] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ContactName' THEN [Suppliers].[ContactName] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'CompanyName' THEN [Suppliers].[CompanyName] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'CompanyName' THEN [Suppliers].[CompanyName] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'City' THEN [Suppliers].[City] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'City' THEN [Suppliers].[City] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Address' THEN [Suppliers].[Address] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Address' THEN [Suppliers].[Address] END END

	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Suppliers].[created_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Suppliers].[created_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Suppliers].[created_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Suppliers].[created_date] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Suppliers].[modified_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Suppliers].[modified_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Suppliers].[modified_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Suppliers].[modified_date] END END
DROP TABLE #tmpTable

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_SupplierSelect]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_SupplierSelect]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_SupplierSelect]
AS

SET NOCOUNT ON;

SELECT
		[Suppliers].[supplierid],
	[Suppliers].[region],
	[Suppliers].[postalcode],
	[Suppliers].[phone],
	[Suppliers].[homepage],
	[Suppliers].[fax],
	[Suppliers].[country],
	[Suppliers].[contacttitle],
	[Suppliers].[contactname],
	[Suppliers].[companyname],
	[Suppliers].[city],
	[Suppliers].[address],
	[Suppliers].[created_by],
	[Suppliers].[created_date],
	[Suppliers].[modified_by],
	[Suppliers].[modified_date],
	[Suppliers].[time_stamp]

FROM 
[Suppliers]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

